package com.sghj.utils.excel;

import com.sghj.construction.entity.PlanQuery;
import com.sghj.construction.entity.vo.ConstructionSitePlanVo;
import com.sghj.record.entity.dto.RecordIncallQuery;
import com.sghj.record.entity.dto.RecordOutcallQuery;
import com.sghj.record.entity.vo.RecordIncallRes;
import com.sghj.record.entity.vo.RecordOutcallRes;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.awt.Color;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @author zk
 * @version 1.0
 * @date 2024-7-19 09:02
 * @description
 */
public class ExcelUtils {


    public static void exportRecordOutcall(HttpServletResponse response, String sheetName, List<RecordOutcallRes> recordOutcallList, RecordOutcallQuery query, String[] title) {

        //新建文档实例
        XSSFWorkbook workbook = new XSSFWorkbook();
        //在文档中添加表单
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //创建单元格格式，并设置居中
        //表头样式
        XSSFCellStyle headStyle = workbook.createCellStyle();
        //具体内容样式
        XSSFCellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setWrapText(true);
        // 设置内容水平居中
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置内容垂直居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //标题样式
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        //第二行样式

        XSSFCellStyle r11Style = workbook.createCellStyle();
        //横向居中
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentStyle.setAlignment(HorizontalAlignment.CENTER);

        // 标题边框样式，竖向居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 横向居中
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        titleStyle.setBorderBottom(BorderStyle.NONE);
        titleStyle.setBorderLeft(BorderStyle.NONE);
        titleStyle.setBorderRight(BorderStyle.NONE);
        titleStyle.setBorderTop(BorderStyle.NONE);

        //表头单元格边框样式
        //下边框
        headStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        headStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        headStyle.setBorderRight(BorderStyle.THIN);
        //上边框
        headStyle.setBorderTop(BorderStyle.THIN);

        r11Style.setBorderBottom(BorderStyle.NONE);
        //左边框
        r11Style.setBorderLeft(BorderStyle.NONE);
        //右边框
        r11Style.setBorderRight(BorderStyle.NONE);
        //上边框
        r11Style.setBorderTop(BorderStyle.NONE);

        //具体内容边框样式
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setBorderTop(BorderStyle.THIN);

        //表头字体样式
        XSSFFont headFont = workbook.createFont();
        //字体名称
        headFont.setFontName("宋体");
        //加粗
        headFont.setBold(false);
        //行高
        headFont.setFontHeightInPoints((short) 13);
//            font.setColor(Font.COLOR_NORMAL);
        //字体颜色
        headFont.setColor(new XSSFColor(new Color(0, 0, 0)));
        headStyle.setFont(headFont);

        // 标题字体样式
        XSSFFont titleFont = workbook.createFont();
        titleFont.setColor(new XSSFColor(new Color(0, 0, 0)));
        // 加粗
        titleFont.setBold(true);
        // 字体名称
        titleFont.setFontName("黑体");
        // 字体大小
        titleFont.setFontHeightInPoints((short) 20);
        // 标题样式
        titleStyle.setFont(titleFont);
        r11Style.setFont(headFont);

        //内容字体样式
        XSSFFont contentFont = workbook.createFont();
        contentFont.setColor(new XSSFColor(new Color(0, 0, 0)));
        contentFont.setBold(false);
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 12);
        contentStyle.setFont(contentFont);

        //创建无边框样式,字体大小同主内容一样
        XSSFCellStyle noBorder = workbook.createCellStyle();
        noBorder.setAlignment(HorizontalAlignment.RIGHT);
        noBorder.setBorderBottom(BorderStyle.NONE);
        noBorder.setBorderLeft(BorderStyle.NONE);
        noBorder.setBorderRight(BorderStyle.NONE);
        noBorder.setBorderTop(BorderStyle.NONE);
        //字体
        noBorder.setFont(contentFont);

        // 合并单元格（第0行、标题）
        CellRangeAddress cAddress0 = new CellRangeAddress(0, 0, 0, title.length - 1);
        sheet.addMergedRegion(cAddress0);
        //创建第0行,添加标题
        XSSFRow headRow0 = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
            //创建单元格
            XSSFCell cell = headRow0.createCell(i);
            //设置单元格内容
            cell.setCellValue("外呼记录明细表");
            //设置单元格样式
            cell.setCellStyle(titleStyle);
        }

        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分");
        String strDate1 = sdf1.format(query.getQueryStartTime());
        String strDate2 = sdf1.format(query.getQueryEndTime());

        CellRangeAddress cAddress1 = new CellRangeAddress(1, 1, 0, title.length - 1);
        sheet.addMergedRegion(cAddress1);
        XSSFRow headRow1 = sheet.createRow(1);
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
            //创建单元格
            XSSFCell cell = headRow1.createCell(i);
            //设置单元格内容
            cell.setCellValue("统计时间：" + sdf2.format(query.getQueryStartTime()) + "至" + sdf2.format(query.getQueryEndTime()));
            //设置单元格样式
            cell.setCellStyle(headStyle);
        }
//        XSSFCell cer1 = headRow1.createCell(i);
//        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
//        String strDate1 = sdf1.format(query.getQueryStartTime());
//        String strDate2 = sdf1.format(query.getQueryEndTime());
//        cer1.setCellValue("统计时间：" + strDate1 + "至" + strDate2);
//        cer1.setCellStyle(r11Style);

        //创建第一行，用于填充表头(属性)
        XSSFRow headRow = sheet.createRow(2);

        //填充表头（属性）
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
            //创建单元格
            XSSFCell cell = headRow.createCell(i);
            //设置单元格内容
            cell.setCellValue(title[i]);
            //设置单元格样式
            cell.setCellStyle(headStyle);
        }

        //填充内容
        //行号
        int i = 3;

        XSSFRow row;
        for (RecordOutcallRes recordOutcallRes : recordOutcallList) {
            //创建行
            row = sheet.createRow(i);

            //创建单元格
            XSSFCell cell0 = row.createCell(0);
            //设置单元格内容
            cell0.setCellValue(recordOutcallList.indexOf(recordOutcallRes) + 1);
            //设置单元格样式
            cell0.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell1 = row.createCell(1);
            //设置单元格内容
            cell1.setCellValue(recordOutcallRes.getDayPlanNumber());
            //设置单元格样式
            cell1.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell3 = row.createCell(2);
            //设置单元格内容
            cell3.setCellValue(exchangeOutCallResult(recordOutcallRes.getOutCallResult()));
            //设置单元格样式
            cell3.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell4 = row.createCell(3);
            //设置单元格内容
            cell4.setCellValue(recordOutcallRes.getLineType());
            //设置单元格样式
            cell4.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell5 = row.createCell(4);
            //设置单元格内容
            cell5.setCellValue(recordOutcallRes.getLocationPlace());
            //设置单元格样式
            cell5.setCellStyle(contentStyle);

            XSSFCell cell6 = row.createCell(5);
            //设置单元格内容
            cell6.setCellValue(recordOutcallRes.getConstructionSiteName());
            //设置单元格样式
            cell6.setCellStyle(contentStyle);

            XSSFCell cell7 = row.createCell(6);
            //设置单元格内容
            if (recordOutcallRes.getStartTime() != null) {
                cell7.setCellValue(sdf1.format(recordOutcallRes.getStartTime()));
            }
            //设置单元格样式
            cell7.setCellStyle(contentStyle);

            XSSFCell cell8 = row.createCell(7);
            //设置单元格内容
            if (recordOutcallRes.getEndTime() != null) {
                cell8.setCellValue(sdf1.format(recordOutcallRes.getEndTime()));
            }
            //设置单元格样式
            cell8.setCellStyle(contentStyle);


            XSSFCell cell9 = row.createCell(8);
            //设置单元格内容
            cell9.setCellValue(recordOutcallRes.getMonitoringPersonnel());
            //设置单元格样式
            cell9.setCellStyle(contentStyle);

            XSSFCell cell10 = row.createCell(9);
            //设置单元格内容
            cell10.setCellValue(recordOutcallRes.getMonitoringPhone());
            //设置单元格样式
            cell10.setCellStyle(contentStyle);

            XSSFCell cell11 = row.createCell(10);
            //设置单元格内容
            cell11.setCellValue(sdf1.format(recordOutcallRes.getCreateTime()));
            //设置单元格样式
            cell11.setCellStyle(contentStyle);

            i++;
        }
        //自适应列宽，循环体之外执行
        for (int j = 0; j < title.length; j++) {
            sheet.autoSizeColumn(j);
        }

        // 设置列宽度
        sheet.setColumnWidth(4, 15 * 256); // 设置第6列的宽度为10个字符宽度
        sheet.setColumnWidth(6, 17 * 256); // 设置第6列的宽度为10个字符宽度
        sheet.setColumnWidth(7, 17 * 256); // 设置第6列的宽度为10个字符宽度
        sheet.setColumnWidth(10, 17 * 256); // 设置第6列的宽度为10个字符宽度


//        CellRangeAddress cAddress2 = new CellRangeAddress(i + 2, i + 2, 0, title.length - 1);
//        sheet.addMergedRegion(cAddress2);
//        //创建表尾
//        XSSFRow headRowFoot = sheet.createRow(i + 2);
//        XSSFCell cellb1 = headRowFoot.createCell(0);
//        cellb1.setCellStyle(r11Style);
//        cellb1.setCellValue("单位负责人（签字并加盖公章）：");

//        CellRangeAddress cAddress3 = new CellRangeAddress(i + 2 + 2, i + 2 + 2, 0, title.length - 1);
//        sheet.addMergedRegion(cAddress3);
//        //创建表尾
//        XSSFRow headRowFoot1 = sheet.createRow(i + 2 + 2);
//        XSSFCell cellb2 = headRowFoot1.createCell(0);
//        cellb2.setCellStyle(r11Style);
//        cellb2.setCellValue("负责人（签字）：                         经办人（签字）：");


        XSSFPrintSetup printSetup = (XSSFPrintSetup) sheet.getPrintSetup();
        //所有列到汇总到一个页面
        printSetup.setFitHeight((short) 0);
        sheet.setFitToPage(true);
        printSetup.setLandscape(true); // 打印方向，true：横向，false：纵向
        printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); //纸张大小，自选A4
//        XSSFPrintSetup printSetup = (XSSFPrintSetup) sheet.getPrintSetup();
//        //所有列到汇总到一个页面
//        sheet.setHorizontallyCenter(true);//设置打印页面为水平居中
////        printSetup.setFitHeight((short) 0);
//        sheet.setFitToPage(true);
//        printSetup.setLandscape(true); // 打印方向，true：横向，false：纵向
//        printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); //纸张大小，自选A4

        CellRangeAddress columnRangeRef = new CellRangeAddress(0, 2, 0, title.length - 1);
//        sheet.setRepeatingColumns(columnRangeRef);
        sheet.setRepeatingRows(columnRangeRef);
        //设置页交
        Footer footer = sheet.getFooter();
        footer.setCenter("第" + HSSFFooter.page() + "页，共" + HSSFFooter.numPages() + "页");


        //声明输出流
        OutputStream outputStream = null;
        //响应到客户端
        try {
            //表格文件名称
            String fileName = sheetName + ".xlsx";
            //设置响应头
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

            //获取输出流
            outputStream = response.getOutputStream();

            //用文档写输出流
            workbook.write(outputStream);

            //刷新输出流
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭输出流
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void exportRecordIncall(HttpServletResponse response, String sheetName, List<RecordIncallRes> recordIncallList, RecordIncallQuery query, String[] title) {

        //新建文档实例
        XSSFWorkbook workbook = new XSSFWorkbook();
        //在文档中添加表单
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //创建单元格格式，并设置居中
        //表头样式
        XSSFCellStyle headStyle = workbook.createCellStyle();
        //具体内容样式
        XSSFCellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setWrapText(true);
        // 设置内容水平居中
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置内容垂直居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //标题样式
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        //第二行样式

        XSSFCellStyle r11Style = workbook.createCellStyle();
        //横向居中
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentStyle.setAlignment(HorizontalAlignment.CENTER);

        // 标题边框样式，竖向居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 横向居中
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        titleStyle.setBorderBottom(BorderStyle.NONE);
        titleStyle.setBorderLeft(BorderStyle.NONE);
        titleStyle.setBorderRight(BorderStyle.NONE);
        titleStyle.setBorderTop(BorderStyle.NONE);

        //表头单元格边框样式
        //下边框
        headStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        headStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        headStyle.setBorderRight(BorderStyle.THIN);
        //上边框
        headStyle.setBorderTop(BorderStyle.THIN);

        r11Style.setBorderBottom(BorderStyle.NONE);
        //左边框
        r11Style.setBorderLeft(BorderStyle.NONE);
        //右边框
        r11Style.setBorderRight(BorderStyle.NONE);
        //上边框
        r11Style.setBorderTop(BorderStyle.NONE);

        //具体内容边框样式
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setBorderTop(BorderStyle.THIN);

        //表头字体样式
        XSSFFont headFont = workbook.createFont();
        //字体名称
        headFont.setFontName("宋体");
        //加粗
        headFont.setBold(false);
        //行高
        headFont.setFontHeightInPoints((short) 13);
//            font.setColor(Font.COLOR_NORMAL);
        //字体颜色
        headFont.setColor(new XSSFColor(new Color(0, 0, 0)));
        headStyle.setFont(headFont);

        // 标题字体样式
        XSSFFont titleFont = workbook.createFont();
        titleFont.setColor(new XSSFColor(new Color(0, 0, 0)));
        // 加粗
        titleFont.setBold(true);
        // 字体名称
        titleFont.setFontName("黑体");
        // 字体大小
        titleFont.setFontHeightInPoints((short) 20);
        // 标题样式
        titleStyle.setFont(titleFont);
        r11Style.setFont(headFont);

        //内容字体样式
        XSSFFont contentFont = workbook.createFont();
        contentFont.setColor(new XSSFColor(new Color(0, 0, 0)));
        contentFont.setBold(false);
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 12);
        contentStyle.setFont(contentFont);

        //创建无边框样式,字体大小同主内容一样
        XSSFCellStyle noBorder = workbook.createCellStyle();
        noBorder.setAlignment(HorizontalAlignment.RIGHT);
        noBorder.setBorderBottom(BorderStyle.NONE);
        noBorder.setBorderLeft(BorderStyle.NONE);
        noBorder.setBorderRight(BorderStyle.NONE);
        noBorder.setBorderTop(BorderStyle.NONE);
        //字体
        noBorder.setFont(contentFont);

        // 合并单元格（第0行、标题）
        CellRangeAddress cAddress0 = new CellRangeAddress(0, 0, 0, title.length - 1);
        sheet.addMergedRegion(cAddress0);
        //创建第0行,添加标题
        XSSFRow headRow0 = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
            //创建单元格
            XSSFCell cell = headRow0.createCell(i);
            //设置单元格内容
            cell.setCellValue("回电记录明细表");
            //设置单元格样式
            cell.setCellStyle(titleStyle);
        }

        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分");
        String strDate1 = sdf1.format(query.getQueryStartTime());
        String strDate2 = sdf1.format(query.getQueryEndTime());

        CellRangeAddress cAddress1 = new CellRangeAddress(1, 1, 0, title.length - 1);
        sheet.addMergedRegion(cAddress1);
        XSSFRow headRow1 = sheet.createRow(1);
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
            //创建单元格
            XSSFCell cell = headRow1.createCell(i);
            //设置单元格内容
            cell.setCellValue("统计时间：" + sdf2.format(query.getQueryStartTime()) + "至" + sdf2.format(query.getQueryEndTime()));
            //设置单元格样式
            cell.setCellStyle(headStyle);
        }
//        XSSFCell cer1 = headRow1.createCell(i);
//        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
//        String strDate1 = sdf1.format(query.getQueryStartTime());
//        String strDate2 = sdf1.format(query.getQueryEndTime());
//        cer1.setCellValue("统计时间：" + strDate1 + "至" + strDate2);
//        cer1.setCellStyle(r11Style);

        //创建第一行，用于填充表头(属性)
        XSSFRow headRow = sheet.createRow(2);

        //填充表头（属性）
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
            //创建单元格
            XSSFCell cell = headRow.createCell(i);
            //设置单元格内容
            cell.setCellValue(title[i]);
            //设置单元格样式
            cell.setCellStyle(headStyle);
        }

        //填充内容
        //行号
        int i = 3;

        XSSFRow row;
        for (RecordIncallRes recordIncallRes : recordIncallList) {
            //创建行
            row = sheet.createRow(i);

            //创建单元格
            XSSFCell cell0 = row.createCell(0);
            //设置单元格内容
            cell0.setCellValue(recordIncallList.indexOf(recordIncallRes) + 1);
            //设置单元格样式
            cell0.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell1 = row.createCell(1);
            //设置单元格内容
            cell1.setCellValue(recordIncallRes.getDayPlanNumber());
            //设置单元格样式
            cell1.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell3 = row.createCell(2);
            //设置单元格内容
            cell3.setCellValue(exchangeInCallResult(recordIncallRes.getInCallResult()));
            //设置单元格样式
            cell3.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell4 = row.createCell(3);
            //设置单元格内容
            cell4.setCellValue(recordIncallRes.getLineType());
            //设置单元格样式
            cell4.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell5 = row.createCell(4);
            //设置单元格内容
            cell5.setCellValue(recordIncallRes.getLocationPlace());
            //设置单元格样式
            cell5.setCellStyle(contentStyle);


            XSSFCell cell6 = row.createCell(5);
            //设置单元格内容
            cell6.setCellValue(recordIncallRes.getConstructionSiteName());
            //设置单元格样式
            cell6.setCellStyle(contentStyle);

            XSSFCell cell7 = row.createCell(6);
            //设置单元格内容
            if (recordIncallRes.getStartTime() != null) {
                cell7.setCellValue(sdf1.format(recordIncallRes.getStartTime()));
            }
            //设置单元格样式
            cell7.setCellStyle(contentStyle);

            XSSFCell cell8 = row.createCell(7);
            //设置单元格内容
            if (recordIncallRes.getEndTime() != null) {
                cell8.setCellValue(sdf1.format(recordIncallRes.getEndTime()));
            }
            //设置单元格样式
            cell8.setCellStyle(contentStyle);


            XSSFCell cell9 = row.createCell(8);
            //设置单元格内容
            cell9.setCellValue(recordIncallRes.getMonitoringPersonnel());
            //设置单元格样式
            cell9.setCellStyle(contentStyle);

            XSSFCell cell10 = row.createCell(9);
            //设置单元格内容
            cell10.setCellValue(recordIncallRes.getMonitoringPhone());
            //设置单元格样式
            cell10.setCellStyle(contentStyle);

            XSSFCell cell11 = row.createCell(10);
            //设置单元格内容
            if (recordIncallRes.getInTime() != null) {
                cell11.setCellValue(recordIncallRes.getInPhoneNumber());
            }
            //设置单元格样式
            cell11.setCellStyle(contentStyle);

            XSSFCell cell12 = row.createCell(11);
            //设置单元格内容
            if (recordIncallRes.getInTime() != null) {
                cell12.setCellValue(sdf1.format(recordIncallRes.getInTime()));
            }
            //设置单元格样式
            cell12.setCellStyle(contentStyle);

            i++;
        }
        //自适应列宽，循环体之外执行
        for (int j = 0; j < title.length; j++) {
            sheet.autoSizeColumn(j);
        }

        // 设置列宽度
        sheet.setColumnWidth(4, 15 * 256); // 设置第6列的宽度为10个字符宽度
        sheet.setColumnWidth(6, 17 * 256); // 设置第6列的宽度为10个字符宽度
        sheet.setColumnWidth(7, 17 * 256); // 设置第6列的宽度为10个字符宽度
        sheet.setColumnWidth(11, 17 * 256); // 设置第6列的宽度为10个字符宽度

//        CellRangeAddress cAddress2 = new CellRangeAddress(i + 2, i + 2, 0, title.length - 1);
//        sheet.addMergedRegion(cAddress2);
//        //创建表尾
//        XSSFRow headRowFoot = sheet.createRow(i + 2);
//        XSSFCell cellb1 = headRowFoot.createCell(0);
//        cellb1.setCellStyle(r11Style);
//        cellb1.setCellValue("单位负责人（签字并加盖公章）：");

//        CellRangeAddress cAddress3 = new CellRangeAddress(i + 2 + 2, i + 2 + 2, 0, title.length - 1);
//        sheet.addMergedRegion(cAddress3);
//        //创建表尾
//        XSSFRow headRowFoot1 = sheet.createRow(i + 2 + 2);
//        XSSFCell cellb2 = headRowFoot1.createCell(0);
//        cellb2.setCellStyle(r11Style);
//        cellb2.setCellValue("负责人（签字）：                         经办人（签字）：");


        XSSFPrintSetup printSetup = (XSSFPrintSetup) sheet.getPrintSetup();
        //所有列到汇总到一个页面
        printSetup.setFitHeight((short) 0);
        sheet.setFitToPage(true);
        printSetup.setLandscape(true); // 打印方向，true：横向，false：纵向
        printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); //纸张大小，自选A4
//        XSSFPrintSetup printSetup = (XSSFPrintSetup) sheet.getPrintSetup();
//        //所有列到汇总到一个页面
//        sheet.setHorizontallyCenter(true);//设置打印页面为水平居中
////        printSetup.setFitHeight((short) 0);
//        sheet.setFitToPage(true);
//        printSetup.setLandscape(true); // 打印方向，true：横向，false：纵向
//        printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); //纸张大小，自选A4

        CellRangeAddress columnRangeRef = new CellRangeAddress(0, 2, 0, title.length - 1);
//        sheet.setRepeatingColumns(columnRangeRef);
        sheet.setRepeatingRows(columnRangeRef);
        //设置页交
        Footer footer = sheet.getFooter();
        footer.setCenter("第" + HSSFFooter.page() + "页，共" + HSSFFooter.numPages() + "页");


        //声明输出流
        OutputStream outputStream = null;
        //响应到客户端
        try {
            //表格文件名称
            String fileName = sheetName + ".xlsx";
            //设置响应头
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

            //获取输出流
            outputStream = response.getOutputStream();

            //用文档写输出流
            workbook.write(outputStream);

            //刷新输出流
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭输出流
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void exportPlan(HttpServletResponse response, String sheetName, List<ConstructionSitePlanVo> planList, PlanQuery query, String[] title) {

        //新建文档实例
        XSSFWorkbook workbook = new XSSFWorkbook();
        //在文档中添加表单
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //创建单元格格式，并设置居中
        //表头样式
        XSSFCellStyle headStyle = workbook.createCellStyle();
        //具体内容样式
        XSSFCellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setWrapText(true);
        // 设置内容水平居中
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置内容垂直居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //标题样式
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        //第二行样式

        XSSFCellStyle r11Style = workbook.createCellStyle();
        //横向居中
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        contentStyle.setAlignment(HorizontalAlignment.CENTER);

        // 标题边框样式，竖向居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //垂直居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 横向居中
        titleStyle.setAlignment(HorizontalAlignment.CENTER);

        // 边框
        titleStyle.setBorderBottom(BorderStyle.NONE);
        titleStyle.setBorderLeft(BorderStyle.NONE);
        titleStyle.setBorderRight(BorderStyle.NONE);
        titleStyle.setBorderTop(BorderStyle.NONE);

        //表头单元格边框样式
        //下边框
        headStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        headStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        headStyle.setBorderRight(BorderStyle.THIN);
        //上边框
        headStyle.setBorderTop(BorderStyle.THIN);

        r11Style.setBorderBottom(BorderStyle.NONE);
        //左边框
        r11Style.setBorderLeft(BorderStyle.NONE);
        //右边框
        r11Style.setBorderRight(BorderStyle.NONE);
        //上边框
        r11Style.setBorderTop(BorderStyle.NONE);

        //具体内容边框样式
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setBorderTop(BorderStyle.THIN);

        //表头字体样式
        XSSFFont headFont = workbook.createFont();
        //字体名称
        headFont.setFontName("宋体");
        //加粗
        headFont.setBold(false);
        //行高
        headFont.setFontHeightInPoints((short) 13);
//            font.setColor(Font.COLOR_NORMAL);
        //字体颜色
        headFont.setColor(new XSSFColor(new Color(0, 0, 0)));
        headStyle.setFont(headFont);

        // 标题字体样式
        XSSFFont titleFont = workbook.createFont();
        titleFont.setColor(new XSSFColor(new Color(0, 0, 0)));
        // 加粗
        titleFont.setBold(true);
        // 字体名称
        titleFont.setFontName("黑体");
        // 字体大小
        titleFont.setFontHeightInPoints((short) 20);
        // 标题样式
        titleStyle.setFont(titleFont);
        r11Style.setFont(headFont);

        //内容字体样式
        XSSFFont contentFont = workbook.createFont();
        contentFont.setColor(new XSSFColor(new Color(0, 0, 0)));
        contentFont.setBold(false);
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 12);
        contentStyle.setFont(contentFont);

        //创建无边框样式,字体大小同主内容一样
        XSSFCellStyle noBorder = workbook.createCellStyle();
        noBorder.setAlignment(HorizontalAlignment.RIGHT);
        noBorder.setBorderBottom(BorderStyle.NONE);
        noBorder.setBorderLeft(BorderStyle.NONE);
        noBorder.setBorderRight(BorderStyle.NONE);
        noBorder.setBorderTop(BorderStyle.NONE);
        //字体
        noBorder.setFont(contentFont);

        // 合并单元格（第0行、标题）
        CellRangeAddress cAddress0 = new CellRangeAddress(0, 0, 0, title.length - 1);
        sheet.addMergedRegion(cAddress0);
        //创建第0行,添加标题
        XSSFRow headRow0 = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
            //创建单元格
            XSSFCell cell = headRow0.createCell(i);
            //设置单元格内容
            cell.setCellValue("施工日计划盯控人员外呼系统明细表");
            //设置单元格样式
            cell.setCellStyle(titleStyle);
        }

        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分");
        String strDate1 = sdf1.format(query.getQueryStartTime());
        String strDate2 = sdf1.format(query.getQueryEndTime());

        CellRangeAddress cAddress1 = new CellRangeAddress(1, 1, 0, title.length - 1);
        sheet.addMergedRegion(cAddress1);
        XSSFRow headRow1 = sheet.createRow(1);
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
            //创建单元格
            XSSFCell cell = headRow1.createCell(i);
            //设置单元格内容
            cell.setCellValue("统计时间：" + sdf2.format(query.getQueryStartTime()) + "至" + sdf2.format(query.getQueryEndTime()));
            //设置单元格样式
            cell.setCellStyle(headStyle);
        }
//        XSSFCell cer1 = headRow1.createCell(i);
//        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
//        String strDate1 = sdf1.format(query.getQueryStartTime());
//        String strDate2 = sdf1.format(query.getQueryEndTime());
//        cer1.setCellValue("统计时间：" + strDate1 + "至" + strDate2);
//        cer1.setCellStyle(r11Style);

        //创建第一行，用于填充表头(属性)
        XSSFRow headRow = sheet.createRow(2);

        //填充表头（属性）
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 15 * 256);
            //创建单元格
            XSSFCell cell = headRow.createCell(i);
            //设置单元格内容
            cell.setCellValue(title[i]);
            //设置单元格样式
            cell.setCellStyle(headStyle);
        }

        //填充内容
        //行号
        int i = 3;

        XSSFRow row;
        for (ConstructionSitePlanVo constructionSitePlan : planList) {
            //创建行
            row = sheet.createRow(i);

            //创建单元格
            XSSFCell cell0 = row.createCell(0);
            //设置单元格内容
            cell0.setCellValue(planList.indexOf(constructionSitePlan) + 1);
            //设置单元格样式
            cell0.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell1 = row.createCell(1);
            //设置单元格内容
            cell1.setCellValue(constructionSitePlan.getDayPlanNumber());
            //设置单元格样式
            cell1.setCellStyle(contentStyle);


            //创建单元格
            XSSFCell cell2 = row.createCell(2);
            //设置单元格内容
            cell2.setCellValue(constructionSitePlan.getLineType());
            //设置单元格样式
            cell2.setCellStyle(contentStyle);

            //创建单元格
            XSSFCell cell3 = row.createCell(3);
            //设置单元格内容
            cell3.setCellValue(constructionSitePlan.getLocationPlace());
            //设置单元格样式
            cell3.setCellStyle(contentStyle);


            XSSFCell cell4 = row.createCell(4);
            //设置单元格内容
            cell4.setCellValue(constructionSitePlan.getConstructionSiteName());
            //设置单元格样式
            cell4.setCellStyle(contentStyle);

            XSSFCell cell5 = row.createCell(5);
            //设置单元格内容
            if (constructionSitePlan.getStartTime() != null) {
                cell5.setCellValue(sdf1.format(constructionSitePlan.getStartTime()));
            }
            //设置单元格样式
            cell5.setCellStyle(contentStyle);

            XSSFCell cell6 = row.createCell(6);
            //设置单元格内容
            if (constructionSitePlan.getEndTime() != null) {
                cell6.setCellValue(sdf1.format(constructionSitePlan.getEndTime()));
            }
            //设置单元格样式
            cell6.setCellStyle(contentStyle);


            XSSFCell cell7 = row.createCell(7);
            //设置单元格内容
            cell7.setCellValue(constructionSitePlan.getMonitoringPersonnel());
            //设置单元格样式
            cell7.setCellStyle(contentStyle);

            XSSFCell cell8 = row.createCell(8);
            //设置单元格内容
            cell8.setCellValue(constructionSitePlan.getMonitoringPhone());
            //设置单元格样式
            cell8.setCellStyle(contentStyle);

            XSSFCell cell9 = row.createCell(9);
            //设置单元格内容
            cell9.setCellValue(exchangeIntegerOutCallResult(constructionSitePlan.getCallStatus()));
            //设置单元格样式
            cell9.setCellStyle(contentStyle);

            XSSFCell cell10 = row.createCell(10);
            //设置单元格内容
            if (constructionSitePlan.getCallTime() != null) {
                cell10.setCellValue(sdf1.format(constructionSitePlan.getCallTime()));
            }
            //设置单元格样式
            cell10.setCellStyle(contentStyle);

            XSSFCell cell11 = row.createCell(11);
            //设置单元格内容
            cell11.setCellValue(constructionSitePlan.getCallBackPhone());
            //设置单元格样式
            cell11.setCellStyle(contentStyle);

            XSSFCell cell12 = row.createCell(12);
            //设置单元格内容
            cell12.setCellValue(exchangeIntegerInCallResult(constructionSitePlan.getCallBackStatus()));
            //设置单元格样式
            cell12.setCellStyle(contentStyle);

            XSSFCell cell13 = row.createCell(13);
            //设置单元格内容
            if (constructionSitePlan.getCallBackTime() != null) {
                cell13.setCellValue(sdf1.format(constructionSitePlan.getCallBackTime()));
            }
            //设置单元格样式
            cell13.setCellStyle(contentStyle);

            i++;
        }
        //自适应列宽，循环体之外执行
        for (int j = 0; j < title.length; j++) {
            sheet.autoSizeColumn(j);
        }

        // 设置列宽度
        sheet.setColumnWidth(3, 15 * 256); // 设置第3列的宽度为15个字符宽度
        sheet.setColumnWidth(5, 15 * 256); // 设置第3列的宽度为15个字符宽度
        sheet.setColumnWidth(6, 15 * 256); // 设置第3列的宽度为15个字符宽度
        sheet.setColumnWidth(10, 15 * 256); // 设置第3列的宽度为15个字符宽度
        sheet.setColumnWidth(13, 15 * 256); // 设置第3列的宽度为15个字符宽度

//        CellRangeAddress cAddress2 = new CellRangeAddress(i + 2, i + 2, 0, title.length - 1);
//        sheet.addMergedRegion(cAddress2);
//        //创建表尾
//        XSSFRow headRowFoot = sheet.createRow(i + 2);
//        XSSFCell cellb1 = headRowFoot.createCell(0);
//        cellb1.setCellStyle(r11Style);
//        cellb1.setCellValue("单位负责人（签字并加盖公章）：");

//        CellRangeAddress cAddress3 = new CellRangeAddress(i + 2 + 2, i + 2 + 2, 0, title.length - 1);
//        sheet.addMergedRegion(cAddress3);
//        //创建表尾
//        XSSFRow headRowFoot1 = sheet.createRow(i + 2 + 2);
//        XSSFCell cellb2 = headRowFoot1.createCell(0);
//        cellb2.setCellStyle(r11Style);
//        cellb2.setCellValue("负责人（签字）：                         经办人（签字）：");


        XSSFPrintSetup printSetup = (XSSFPrintSetup) sheet.getPrintSetup();
        //所有列到汇总到一个页面
        printSetup.setFitHeight((short) 0);
        sheet.setFitToPage(true);
        printSetup.setLandscape(true); // 打印方向，true：横向，false：纵向
        printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); //纸张大小，自选A4
//        XSSFPrintSetup printSetup = (XSSFPrintSetup) sheet.getPrintSetup();
//        //所有列到汇总到一个页面
//        sheet.setHorizontallyCenter(true);//设置打印页面为水平居中
////        printSetup.setFitHeight((short) 0);
//        sheet.setFitToPage(true);
//        printSetup.setLandscape(true); // 打印方向，true：横向，false：纵向
//        printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); //纸张大小，自选A4

        CellRangeAddress columnRangeRef = new CellRangeAddress(0, 2, 0, title.length - 1);
//        sheet.setRepeatingColumns(columnRangeRef);
        sheet.setRepeatingRows(columnRangeRef);
        //设置页交
        Footer footer = sheet.getFooter();
        footer.setCenter("第" + HSSFFooter.page() + "页，共" + HSSFFooter.numPages() + "页");


        //声明输出流
        OutputStream outputStream = null;
        //响应到客户端
        try {
            //表格文件名称
            String fileName = sheetName + ".xlsx";
            //设置响应头
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

            //获取输出流
            outputStream = response.getOutputStream();

            //用文档写输出流
            workbook.write(outputStream);

            //刷新输出流
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭输出流
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static String exchangeOutCallResult(String outCallResult) {
        String result = null;
        switch (outCallResult) {
            case "0":
                result = "待确认";
                break;
            case "1":
                result = "已确认";
                break;
            case "2":
                result = "已委托";
                break;
            default:
                result = "未确认";
                break;
        }
        return result;
    }

    private static String exchangeInCallResult(String inCallResult) {
        String result = null;
        switch (inCallResult) {
            case "1":
                result = "已完成";
                break;
            case "2":
                result = "施工延期";
                break;
            case "3":
                result = "施工取消";
                break;
            default:
                result = "未知";
                break;
        }
        return result;
    }

    private static String exchangeIntegerOutCallResult(Integer outCallResult) {
        String result = null;
        switch (outCallResult) {
            case 0:
                result = "待确认";
                break;
            case 1:
                result = "已确认";
                break;
            case 2:
                result = "已委托";
                break;
            default:
                result = "未确认";
                break;
        }
        return result;
    }

    private static String exchangeIntegerInCallResult(Integer inCallResult) {
        String result = null;
        switch (inCallResult) {
            case 0:
                result = "未回电";
                break;
            case 1:
                result = "已完成";
                break;
            case 2:
                result = "施工延期";
                break;
            case 3:
                result = "施工取消";
                break;
            case 4:
                result = "已逾期";
                break;
            default:
                result = "未知";
                break;
        }
        return result;
    }


}
